package com.zzzzzz.content.pictext;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.zzzzzz.common.GlobalConstants;
import com.zzzzzz.content.Constants;
import com.zzzzzz.core.dao.BaseDao;

@Repository
public class PicTextDao extends BaseDao {
	
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	private static final String ADD = 					"INSERT INTO topic(title, text_s, tags, user_id, add_dt, upd_dt, status, type_id, looks, replies, likes) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private static final String EDIT_STATUS = 			"update topic set status = ?, upd_dt = ? where id = ? and user_id = ?";
	private static final String EDIT = 					"update topic set title = ?, text_s = ?, tags = ?, upd_dt = ? where id = ? and user_id = ?";
	private static final String EDIT_PICIDS = 			"update topic set pic_id = ?, upd_dt = ? where id = ? and user_id = ?";
	
	private final static String EDIT_LOOKS = 			"update topic set looks = looks + ? where id = ?";
	private static final String EDIT_REPLIES = 			"update topic set replies = replies + ? where id = ?";
	private final static String EDIT_LIKES = 			"update topic set likes = likes + ? where id = ?";
	
	private static final String FIND_BY_ID = 			"select t.id, t.title, t.text_s, t.tags, t.user_id, t.add_dt, t.upd_dt, t.status, t.looks, t.replies, t.likes from topic t where t.id = ?";
	
	private static final String FIND_BY_USERID = 	   	"select t.id, t.title, t.text_s, t.tags, t.pic_id, t.looks, t.replies, t.likes from topic t where t.user_id = ? and t.status = ?";
	private static final String FIND_BY_USERID_COUNT = 	"select count(1) as count from topic t where t.user_id = ? and t.status = ?";
	
	public void add(PicText picText) {
		jdbcTemplate.update(ADD, picText.getTitle(), picText.getTextS(), picText.getTags(), picText.getUserId(), new Date(), new Date(), picText.getStatus(), Constants.CONTENT_TYPE.PICTEXT, picText.getLooks(), picText.getReplies(), picText.getLikes());
		Long id = jdbcTemplate.queryForLong("SELECT @@IDENTITY");
		picText.setId(id);
	}

	public void editStatus(Long id, Integer status, Long userId) {
		jdbcTemplate.update(EDIT_STATUS, status, new Date(), id, userId);
	}

	public void edit(PicText picText) {
		jdbcTemplate.update(EDIT, picText.getTitle(), picText.getTextS(), picText.getTags(), new Date(), picText.getId(), picText.getUserId());
	}
	
	public void editPicIds(Long id, String picIds, Long userId) {
		jdbcTemplate.update(EDIT_PICIDS, picIds, new Date(), id, userId);
	}
	
	public void editLooks(Long id, Integer looksPlus){
		jdbcTemplate.update(EDIT_LOOKS, looksPlus, id);
	}
	
	public void editLikes(Long id, Integer likesPlus){
		jdbcTemplate.update(EDIT_LIKES, likesPlus, id);
	}
	
	public void editReplies(Long id, Integer repliesPlus){
		jdbcTemplate.update(EDIT_REPLIES, repliesPlus, id);
	}
	
	public PicText findById(Long id){
		return jdbcTemplate.queryForObject(FIND_BY_ID, new RowMapper<PicText>(){
			public PicText mapRow(ResultSet rs, int rowNum) throws SQLException {
				PicText picText = new PicText();
				picText.setId(rs.getLong("id"));
				picText.setTitle(rs.getString("title"));
				picText.setTextS(rs.getString("text_s"));
				picText.setUserId(rs.getLong("user_id"));
				picText.setTags(rs.getString("tags"));
				picText.setAddDt(rs.getTimestamp("add_dt"));
				picText.setUpdDt(rs.getTimestamp("upd_dt"));
				picText.setStatus(rs.getInt("status"));
				picText.setLooks(rs.getInt("looks"));
				picText.setLikes(rs.getInt("likes"));
				picText.setReplies(rs.getInt("replies"));
				return picText;
			}
		}, id);
	}
	
	public List<PicTextListView> findByPicTextId(Long userId, String orderByField, int start, int offset){
		StringBuilder sql = new StringBuilder(FIND_BY_USERID);
		sql.append(orderBy(orderByField));
		sql.append(limit());
		
		return jdbcTemplate.query(FIND_BY_USERID, new RowMapper<PicTextListView>(){
			public PicTextListView mapRow(ResultSet rs, int rowNum) throws SQLException {
				PicTextListView picTextListView = new PicTextListView();
				picTextListView.setId(rs.getLong("id"));
				picTextListView.setTitle(rs.getString("title"));
				picTextListView.setTextS(rs.getString("text_s"));
				picTextListView.setTags(rs.getString("tags"));
				picTextListView.setPicIds(rs.getString("pic_id"));
				picTextListView.setLooks(rs.getInt("looks"));
				picTextListView.setReplies(rs.getInt("replies"));
				picTextListView.setLikes(rs.getInt("likes"));
				return picTextListView;
			}
		}, userId, GlobalConstants.STATUS.NORMAL, start, offset);
	}
	
	public long findByPicTextIdCount(Long userId){
		return jdbcTemplate.queryForLong(FIND_BY_USERID_COUNT, userId, GlobalConstants.STATUS.NORMAL);
	}
}
